﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using KVB.Common;

namespace Customer_Profile.eCard.DBDao
{
    public class AccountingDao
    {
        public static void CardAccountingCheck(string card_no, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@card_no", SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = card_no;                
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Accounting_check", parameters);

                review_status = parameters[1].Value.ToString();
                strMessage = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.CardAccountingCheck", ex.Message);
                review_status = "";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void getCardBalance(string card_no, out string card_balance, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@card_no", SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@card_balance", SqlDbType.Decimal),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };

                parameters[0].Value = card_no;

                parameters[1].Direction = ParameterDirection.Output;                
                parameters[1].Precision = (byte)18;
                parameters[1].Scale = (byte)2;

                parameters[2].Direction = ParameterDirection.Output;
                parameters[3].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_card_balance", parameters);

                card_balance = parameters[1].Value.ToString();
                review_status = parameters[2].Value.ToString();
                strMessage = parameters[3].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.getCardBalance", ex.Message);
                review_status = "";
                strMessage = ex.ToString();
                card_balance = "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void getCardBalance(string card_no, out string card_balance)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                string str_sql ="select card_balance from card_balance WHERE card_no='"+card_no.Trim()+"'";
                DataSet ds = DB.ExecuteDataset(conn, CommandType.Text,str_sql );
                if (!Comm.DataSetIsEmpty(ds))
                {
                    card_balance = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    card_balance = "";
                }
                Log.WriteDefaultLog("余额：" + str_sql, card_balance);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.getCardBalance1-update", ex.Message);                
                card_balance = "";
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static DataSet getDealsbyCardno(string card_no)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@card_no", SqlDbType.VarChar,50)                                             
                                           };
                parameters[0].Value = card_no;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Deals_bycardno", parameters);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.getDealsbyCardno", ex.Message);                
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getDealsbyExpenseno(string expense_no)
        {
            DataSet ds = null;
            SqlConnection conn = null;
            string str_SQL = "SELECT card_no,deal_no,amount,cflow_type,pass_remark,reject_remark,remark,review_date FROM dbo.expense_deals ed WHERE expense_no=" + expense_no;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, str_SQL);

            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.getDealsbyExpenseno", ex.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }
    
        public static void cardRecharge(string card_no,int deal_no,string cflow_type,decimal cflow_amount,string cflow_remark,
            string cflow_attach_url,
            string user_id,string user_name, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@card_no", SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@deal_no", SqlDbType.Int),
                                             new SqlParameter("@cflow_type", SqlDbType.VarChar,50),
                                             new SqlParameter("@cflow_amount", SqlDbType.Decimal),
                                             new SqlParameter("@cflow_remark", SqlDbType.VarChar,250),    
                                             new SqlParameter("@cflow_attach_url", SqlDbType.VarChar,250),    
                                             new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@user_name", SqlDbType.VarChar,50),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = card_no;
                parameters[1].Value = deal_no;
                parameters[2].Value = cflow_type;
                parameters[3].Value = cflow_amount;
                parameters[4].Value = cflow_remark;
                parameters[5].Value = cflow_attach_url;
                parameters[6].Value = user_id;
                parameters[7].Value = user_name;
                parameters[8].Direction = ParameterDirection.Output;
                parameters[9].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_ecard_recharge", parameters);
                                
                review_status = parameters[8].Value.ToString();
                strMessage = parameters[9].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.cardRecharge", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();                
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardExpenseAdd(string card_no, int deal_no, string cflow_type, decimal cflow_amount, string cflow_remark,          
           string user_id, string user_name, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@card_no", SqlDbType.VarChar,50),                                             
                                             new SqlParameter("@deal_no", SqlDbType.Int),
                                             new SqlParameter("@cflow_type", SqlDbType.VarChar,50),
                                             new SqlParameter("@cflow_amount", SqlDbType.Decimal),
                                             new SqlParameter("@cflow_remark", SqlDbType.VarChar,250),    
                                             //new SqlParameter("@cflow_attach_url", SqlDbType.VarChar,250),    
                                             new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@user_name", SqlDbType.VarChar,50),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = card_no;
                parameters[1].Value = deal_no;
                parameters[2].Value = cflow_type;
                parameters[3].Value = cflow_amount;
                parameters[4].Value = cflow_remark;
                //parameters[5].Value = cflow_attach_url;
                parameters[5].Value = user_id;
                parameters[6].Value = user_name;
                parameters[7].Direction = ParameterDirection.Output;
                parameters[8].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Add", parameters);

                review_status = parameters[7].Value.ToString();
                strMessage = parameters[8].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Add", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardExpenseUpdate(int expense_no, int deal_no, string cflow_type, decimal cflow_amount, string cflow_remark,
           string user_id, string user_name, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={                                              
                                             new SqlParameter("@expense_no", SqlDbType.Int),
                                             new SqlParameter("@deal_no", SqlDbType.Int),
                                             new SqlParameter("@cflow_type", SqlDbType.VarChar,50),
                                             new SqlParameter("@cflow_amount", SqlDbType.Decimal),
                                             new SqlParameter("@cflow_remark", SqlDbType.VarChar,250),    
                                             new SqlParameter("@user_id", SqlDbType.VarChar,50),
                                             new SqlParameter("@user_name", SqlDbType.VarChar,50),
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = expense_no;
                parameters[1].Value = deal_no;
                parameters[2].Value = cflow_type;
                parameters[3].Value = cflow_amount;
                parameters[4].Value = cflow_remark;
                parameters[5].Value = user_id;
                parameters[6].Value = user_name;
                parameters[7].Direction = ParameterDirection.Output;
                parameters[8].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Update", parameters);

                review_status = parameters[7].Value.ToString();
                strMessage = parameters[8].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Update", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardExpenseSubmit(int expense_no, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
                                             new SqlParameter("@expense_no", SqlDbType.Int),                                             
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = expense_no;                
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Submit", parameters);

                review_status = parameters[1].Value.ToString();
                strMessage = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Submit", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardExpensePass(int expense_no, string pass_remark,string user_id,string user_name, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
                                             new SqlParameter("@expense_no", SqlDbType.Int),                                             
                                             new SqlParameter("@pass_remark", SqlDbType.VarChar,500),      
                                             new SqlParameter("@user_id", SqlDbType.VarChar,500),      
                                             new SqlParameter("@user_name", SqlDbType.VarChar,500),      
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,500)
                                           };
                parameters[0].Value = expense_no;
                parameters[1].Value = pass_remark;
                parameters[2].Value = user_id;
                parameters[3].Value = user_name;
                parameters[4].Direction = ParameterDirection.Output;
                parameters[5].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Pass", parameters);

                review_status = parameters[4].Value.ToString();
                strMessage = parameters[5].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Pass", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }

        public static void cardExpenseBack(int expense_no, string back_remark, string user_id, string user_name, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
                                             new SqlParameter("@expense_no", SqlDbType.Int),                                             
                                             new SqlParameter("@back_remark", SqlDbType.VarChar,500),      
                                             new SqlParameter("@user_id", SqlDbType.VarChar,500),      
                                             new SqlParameter("@user_name", SqlDbType.VarChar,500),      
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,500)
                                           };
                parameters[0].Value = expense_no;
                parameters[1].Value = back_remark;
                parameters[2].Value = user_id;
                parameters[3].Value = user_name;
                parameters[4].Direction = ParameterDirection.Output;
                parameters[5].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Back", parameters);

                review_status = parameters[4].Value.ToString();
                strMessage = parameters[5].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Back", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }
        public static void cardExpenseDelete(int expense_no, out string review_status, out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={
                                             new SqlParameter("@expense_no", SqlDbType.Int),                                             
                                             new SqlParameter("@review_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = expense_no;
                parameters[1].Direction = ParameterDirection.Output;
                parameters[2].Direction = ParameterDirection.Output;

                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_Expense_Delete", parameters);

                review_status = parameters[1].Value.ToString();
                strMessage = parameters[2].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.eCard_Expense_Delete", ex.Message);
                review_status = "N";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }
        public static DataSet getExpenseDealList(string strWhere)          //搜索Deals
        {
            string strSQL = "SELECT expense_no,cflow_no,card_no,deal_no,amount,expense_status,cflow_type,input_user,input_date,";
            strSQL += " (SELECT deal_balance FROM dbo.mmdeals WHERE deal_no=ed.deal_no) AS deal_balance,";
            strSQL += " (SELECT CodeName FROM dbo.TP_TCode WHERE TypeID=(SELECT ID FROM dbo.TP_TType WHERE codeTypeName='ExpenseDealStatus') AND CodeValue=ed.expense_status) AS expense_status_name,";
            strSQL += " (SELECT CodeName FROM dbo.TP_TCode WHERE TypeID=(SELECT ID FROM dbo.TP_TType WHERE codeTypeName='cflow_type') AND CodeValue=ed.cflow_type) AS cflow_type_name";
            strSQL += " FROM dbo.expense_deals ed ";
            strSQL += strWhere;
            DataSet ds = null;
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                ds = DB.ExecuteDataset(conn, CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("DealDao.getExpenseDealList", ex.Message + "SQL:" + strSQL);
                throw ex;
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static DataSet getDealAllInfo_byExpenseno(int expense_no)
        {
            SqlConnection conn = null;
            DataSet ds = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@expense_no", SqlDbType.Int)                                             
                                           };
                parameters[0].Value = expense_no;
                ds = DB.ExecuteDataset(conn, CommandType.StoredProcedure, "eCard_Expense_CheckInfo", parameters);
            }
            catch (Exception e)
            {
                KVB.Common.Log.WriteDefaultLog("netBankDao.getDealAllInfo", e.Message);
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
            return ds;
        }

        public static void ResetPassword(string login_id,out string reset_status,out string strMessage)
        {
            SqlConnection conn = null;
            try
            {
                DB.Connection(ref conn);
                SqlParameter[] parameters ={ 
                                             new SqlParameter("@login_id", SqlDbType.VarChar,50),   
                                             new SqlParameter("@user_password", SqlDbType.VarChar,50),   
                                             new SqlParameter("@reset_status", SqlDbType.VarChar,50),
                                             new SqlParameter("@message", SqlDbType.VarChar,250)
                                           };
                parameters[0].Value = login_id;
                parameters[1].Value = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile("123456", "MD5").ToLower();
                parameters[2].Direction = ParameterDirection.Output;
                parameters[3].Direction = ParameterDirection.Output;
                DB.ExecuteNonQuery(conn, CommandType.StoredProcedure, "eCard_User_ResetPassword", parameters);

                reset_status = parameters[2].Value.ToString();
                strMessage = parameters[3].Value.ToString();
            }
            catch (Exception ex)
            {
                KVB.Common.Log.WriteDefaultLog("AccountingDao.ResetPassword:"+login_id, ex.Message);
                reset_status = "";
                strMessage = ex.ToString();
            }
            finally
            {
                DB.ConnDispose(ref conn);
            }
        }
    }
}